﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Web.Security;
using BusinessLogic;

namespace DataAccessLogic
{
    public static class ConstituentDataAccess
    {
        #region Methods
        private static Constituent BindRowToConstituent(DataRow row)
        {
            Constituent c = new Constituent();
            c.ID = DataAccess.CheckForGuidColumn(row, "ID");
            c.FirstName = DataAccess.CheckForStringColumn(row, "FIRSTNAME");
            c.MiddleName = DataAccess.CheckForStringColumn(row, "MIDDLENAME");
            c.LastName = DataAccess.CheckForStringColumn(row, "LASTNAME");
            c.IsOrganization = DataAccess.CheckForBooleanColumn(row, "ISORGANIZATION");
            c.LastNamePrefix = DataAccess.CheckForStringColumn(row, "LASTNAMEPREFIX");
            c.IsPrimary = DataAccess.CheckForBooleanColumn(row, "ISPRIMARY");
            c.IsActive = DataAccess.CheckForBooleanColumn(row, "ISACTIVE");
            c.IsSales = DataAccess.CheckForBooleanColumn(row, "ISSALES");
            c.IsService = DataAccess.CheckForBooleanColumn(row, "ISSERVICE");
            c.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
            c.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
            c.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
            c.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");
            c.Address = Address.GetPrimaryByConstituentID(c.ID);
            c.Phone = Phone.GetPrimaryByConstituentID(c.ID);
            c.EmailAddress = Email.GetPrimaryByConstituentID(c.ID);
            
            return c;
        }

        public static List<Constituent> CheckForDuplicates(string name)
        {
            List<Constituent> constituents = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@NAME", name));

                constituents = Get(StoredProcedures.USP_CONSTITUENT_CHECKFORDUPLICATES, parms);
            }

            return constituents;
        }

        public static Guid CreateForConversion(Constituent constituent)
        {
            return Create(constituent, true);
        }

        public static Guid Create(Constituent constituent)
        {
            return Create(constituent, false);
        }

        public static Guid Create(Constituent constituent, bool isConversion)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid();
            
            try 
	        {	        
	            providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());	
	        }
	        catch 
	        {
                providerUserKey = new Guid("B6E747BD-F3D5-482A-9785-9FEF877D5B6C");
	        }

            if(isConversion)
            {
                providerUserKey = new Guid("c9014ff9-ee89-4192-aa6b-b0c2130557ba");
            }

            string outputParameterName = "@ID";

            if(constituent != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);
                parms.Add(new SqlParameter("@FIRSTNAME", constituent.FirstName));
                parms.Add(new SqlParameter("@MIDDLENAME", constituent.MiddleName));
                parms.Add(new SqlParameter("@LASTNAME", constituent.LastName));
                parms.Add(new SqlParameter("@ISORGANIZATION", constituent.IsOrganization));
                parms.Add(new SqlParameter("@LASTNAMEPREFIX", constituent.LastNamePrefix));
                parms.Add(new SqlParameter("@ISPRIMARY", constituent.IsPrimary));
                parms.Add(new SqlParameter("@ISACTIVE", constituent.IsActive));
                parms.Add(new SqlParameter("@ISSALES", constituent.IsSales));
                parms.Add(new SqlParameter("@ISSERVICE", constituent.IsService));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_CONSTITUENT_CREATE, outputParameterName, parms);

                if(obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid id)
        {
            if(id != Guid.Empty)
            {
                DataAccess.Delete(StoredProcedures.USP_CONSTITUENT_DELETE, id);
            }
        }

        private static List<Constituent> Get(string storedProcedure)
        {
            return Get(storedProcedure, null);
        }

        private static List<Constituent> Get(string storedProcedure, List<SqlParameter> parms)
        {
            List<Constituent> constituents = null;

            DataTable dt = DataAccess.Get(storedProcedure, parms);

            if (null != dt)
            {
                constituents = new List<Constituent>();

                foreach (DataRow row in dt.Rows)
                {
                    constituents.Add(BindRowToConstituent(row));
                }
            }

            return constituents;
        }

        public static List<Constituent> GetActive()
        {
            return Get(StoredProcedures.USP_CONSTITUENT_GETACTIVE);
        }
        
        public static List<string> GetActiveNames()
        {
            List<string> names = null;

            DataTable dt = DataAccess.Get(StoredProcedures.USP_CONSTITUENT_GETACTIVENAMES);

            if (null != dt)
            {
                names = new List<string>();

                foreach (DataRow row in dt.Rows)
                {
                    names.Add(row["NAME"].ToString());
                }
            }

            return names;
        }

        public static DataTable GetActivePrimariesDataTable()
        {
            DataTable dt = DataAccess.Get(StoredProcedures.USP_CONSTITUENT_GETACTIVEPRIMARIES, null);

            //if (null != dt)
            //{
            //    constituents = new List<Constituent>();

            //    foreach (DataRow row in dt.Rows)
            //    {
            //        Constituent c = new Constituent();
            //        c.ID = DataAccess.CheckForGuidColumn(row, "ID");
            //        c.Name = DataAccess.CheckForStringColumn(row, "ORGANIZATION");                                        
            //        c.PrimaryContact = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "PRIMARYCONTACT") };                                        
            //        c.Phone = new Phone() { Number = DataAccess.CheckForStringColumn(row, "PHONENUMBER") };
            //        c.EmailAddress = new Email() { EmailAddress = DataAccess.CheckForStringColumn(row, "EMAILADDRESS") };

            //        //Address add = new Address();
            //        //c.Address = add;

            //        constituents.Add(c);
            //    }
            //}

            return dt;
        }

        public static List<Constituent> GetAll()
        {
            return Get(StoredProcedures.USP_CONSTITUENT_GETALL);
        }

        public static Constituent GetByID(Guid id)
        {
            Constituent constituent = null;

            if(id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));
                
                List<Constituent> cs = Get(StoredProcedures.USP_CONSTITUENT_GETBYID, parms);

                if (cs != null && cs.Count > 0)
                {
                    constituent = cs[0];
                }
            }

            return constituent;
        }
                
        public static void Mark(bool inActive, Guid id)
        {
            if(id != Guid.Empty)
            {
                string storedProcedure = StoredProcedures.USP_CONSTITUENT_MARKACTIVE;
                Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());

                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                if(inActive)
                {
                    storedProcedure = StoredProcedures.USP_CONSTITUENT_MARKINACTIVE;
                }

                DataAccess.Update(storedProcedure, parms);
            }
        }
        
        public static List<Constituent> Search(string name)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@NAME", name));

            return Get(StoredProcedures.USP_CONSTITUENT_SEARCHBYNAME, parms);         
        }

        public static void Update(Constituent constituent)
        {
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            
            if (constituent != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", constituent.ID));
                parms.Add(new SqlParameter("@FIRSTNAME", constituent.FirstName));
                parms.Add(new SqlParameter("@MIDDLENAME", constituent.MiddleName));
                parms.Add(new SqlParameter("@LASTNAME", constituent.LastName));
                parms.Add(new SqlParameter("@ISORGANIZATION", constituent.IsOrganization));
                parms.Add(new SqlParameter("@LASTNAMEPREFIX", constituent.LastNamePrefix));
                parms.Add(new SqlParameter("@ISPRIMARY", constituent.IsPrimary));
                parms.Add(new SqlParameter("@ISACTIVE", constituent.IsActive));
                parms.Add(new SqlParameter("@ISSALES", constituent.IsSales));
                parms.Add(new SqlParameter("@ISSERVICE", constituent.IsService));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                DataAccess.Update(StoredProcedures.USP_CONSTITUENT_UPDATE, parms);
            }
        }

        public static DataTable SearchAll()
        {
            return DataAccess.Get(StoredProcedures.USP_CONSTITUENT_SEARCH);
        }

        public static void ConversionPrep()
        {
            DataAccess.Delete(StoredProcedures.USP_CONVERSIONPREP, null);
        }
        #endregion
    }
}